﻿Imports System.Data
Imports System.Data.SqlClient

Namespace DB

    ''' <summary>
    ''' TypeName:    CatalogItemType
    ''' Author:      GeneratedCustomTemplate
    ''' DBTableName: SQC_Catalog
    ''' </summary>
    ''' <remarks></remarks>

    Public Class CatalogItemType
        Inherits DBTableObjectBase

#Region "Declarations"
        'Protected Friend iEquipmentId as Guid
        Protected Friend iEquipmentType As String = "Mobile Phone"
        Protected Friend iManufacturerId As Guid
        Protected Friend iManufacturerName As String
        Protected Friend iProductModelNumber As String
        Protected Friend iName As String
        Protected Friend iDescription As String
        Protected Friend iPicture As Drawing.Image
        Protected Friend iFeatures As New FeaturesTypeCollection(Me.iId)
#End Region

#Region "Constructors"
        Public Sub New()
            MyBase.New(TableNamesEnum.SQC_Catalog)
        End Sub
        Public Sub New(ByVal EquipmentId As Guid)
            MyBase.New(TableNamesEnum.SQC_Catalog, EquipmentId)
        End Sub
        Public Sub New(ByVal EquipmentId As Guid, _
                        ByVal EquipmentType As String, _
                        ByVal ManufacturerId As Guid, _
                        ByVal ProductModelNumber As String, _
                        ByVal Name As String, _
                        ByVal Description As String, _
                        ByVal Picture As Drawing.Image, _
                        ByVal LMUserId As Guid, _
                        ByVal LMDate As DateTime)
            MyBase.New(TableNamesEnum.SQC_Catalog)
            Me.iId = EquipmentId
            Me.iEquipmentType = EquipmentType
            Me.iManufacturerId = ManufacturerId
            Me.iProductModelNumber = ProductModelNumber
            Me.iName = Name
            Me.iDescription = Description
            Me.iPicture = Picture
            Me.iLMUserId = LMUserId
            Me.iLMDate = LMDate

        End Sub
#End Region

#Region "Exposed Properties"
        Public Property EquipmentId() As Guid
            Get
                Return Me.iId
            End Get
            Set(ByVal value As Guid)
                Me.iId = value
            End Set
        End Property
        Public Property EquipmentType() As SQC_EquipmentTypesEnum
            Get
                Return [Enum].Parse(GetType(SQC_EquipmentTypesEnum), Me.iEquipmentType.Replace(" ", "_"))
            End Get
            Set(ByVal value As SQC_EquipmentTypesEnum)
                Me.iEquipmentType = value.ToString.Replace("_", " ")
            End Set
        End Property
        Public Property ManufacturerId() As Guid
            Get
                Return Me.iManufacturerId
            End Get
            Set(ByVal value As Guid)
                Me.iManufacturerId = value
            End Set
        End Property
        Public Property ManufacturerName As String
            Get
                Return Me.iManufacturerName
            End Get
            Set(value As String)
                Me.iManufacturerName = value
            End Set
        End Property
        Public Property ProductModelNumber() As String
            Get
                Return Me.iProductModelNumber
            End Get
            Set(ByVal value As String)
                Me.iProductModelNumber = value
            End Set
        End Property
        Public Property Name() As String
            Get
                Return Me.iName
            End Get
            Set(ByVal value As String)
                Me.iName = value
            End Set
        End Property
        Public Property Description() As String
            Get
                Return Me.iDescription
            End Get
            Set(ByVal value As String)
                Me.iDescription = value
            End Set
        End Property
        Public Property Picture() As Drawing.Image
            Get
                Return Me.iPicture
            End Get
            Set(ByVal value As Drawing.Image)
                Me.iPicture = value
            End Set
        End Property
        Public Property Features As FeaturesTypeCollection
            Get
                Return Me.iFeatures
            End Get
            Set(value As FeaturesTypeCollection)
                Me.iFeatures = value
            End Set
        End Property
#End Region

#Region "Implementations"
        Protected Friend Overloads Function Load(ByVal Row As DataRow) As Boolean
            If IsDBNull(Row(0)) Then Me.iId = Guid.Empty Else Me.iId = Row(0)
            If IsDBNull(Row(1)) Then Me.iEquipmentType = Nothing Else Me.iEquipmentType = Row(1)
            If IsDBNull(Row(2)) Then Me.iManufacturerId = Nothing Else Me.iManufacturerId = Row(2)
            'ManufacturerName
            If IsDBNull(Row(3)) Then Me.iManufacturerName = Nothing Else Me.iManufacturerName = Row(3)
            If IsDBNull(Row(4)) Then Me.iProductModelNumber = Nothing Else Me.iProductModelNumber = Row(4)
            If IsDBNull(Row(5)) Then Me.iName = Nothing Else Me.iName = Row(5)
            If IsDBNull(Row(6)) Then Me.iDescription = Nothing Else Me.iDescription = Row(6)
            If IsDBNull(Row(7)) Then
                Me.iPicture = Nothing
            Else
                Dim buffer() As Byte = Row(7)
                Dim ms As New IO.MemoryStream(buffer)
                Me.iPicture = Drawing.Image.FromStream(ms)
            End If
            If IsDBNull(Row(8)) Then Me.iLMUserId = Nothing Else Me.iLMUserId = Row(8)
            If IsDBNull(Row(9)) Then Me.iLMDate = Nothing Else Me.iLMDate = Row(9)

            'Load related data
            'Features
            Me.iFeatures = New FeaturesTypeCollection(Me.iId)
            With Me.iFeatures
                .Connection = Me.Connection
                .Transaction = Me.Transaction
                .Load()
            End With

            Return True
        End Function
        Protected Friend Overrides Function Load() As Boolean
            'sp_[TableName]Get
            Dim resBool As Boolean = True
            Dim da As New SqlDataAdapter
            Dim cmd As New SqlCommand("sp_" & Me.iTableName.ToString.Replace("COMP_", "") & "Get '" & Me.iId.ToString("D") & "'")
            cmd.Connection = Me.iConnection
            If IsNothing(Me.iTransaction) = False Then cmd.Transaction = Me.iTransaction

            Dim dt As DataTable = New DataTable
            dt = Utility.GetList(cmd)

            If dt.Rows.Count > 0 Then
                Me.Load(dt.Rows(0))
            Else
                resBool = False
            End If

            Return resBool
        End Function
        Protected Friend Overrides Function Insert() As Boolean
            'sp_[TableName]Insert
            Dim resBool As Boolean = True
            Dim resInt As Integer
            Dim da As New SqlDataAdapter

            Dim cmd As New SqlCommand("sp_" & Me.iTableName.ToString.Replace("COMP_", "") & "Insert")
            cmd.Connection = Me.iConnection
            If IsNothing(Me.iTransaction) = False Then cmd.Transaction = Me.iTransaction
            'Set command type to Stoed Procedure
            cmd.CommandType = CommandType.StoredProcedure
            'Validation or required parameters
            Try
                'Populate Stored Procedure parameters
                With cmd.Parameters
                    If Me.iId = Guid.Empty Then
                        .AddWithValue("@EquipmentId", System.DBNull.Value)
                    Else
                        .AddWithValue("@EquipmentId", Me.iId)
                    End If
                    If Me.iEquipmentType = "" Then
                        .AddWithValue("@EquipmentType", System.DBNull.Value)
                    Else
                        .AddWithValue("@EquipmentType", Me.iEquipmentType)
                    End If
                    If Me.iManufacturerId = Guid.Empty Then
                        .AddWithValue("@ManufacturerId", System.DBNull.Value)
                    Else
                        .AddWithValue("@ManufacturerId", Me.iManufacturerId)
                    End If
                    If Me.iManufacturerName = String.Empty Then
                        .AddWithValue("@ManufacturerName", System.DBNull.Value)
                    Else
                        .AddWithValue("@ManufacturerName", Me.iManufacturerName)
                    End If
                    If Me.iProductModelNumber = String.Empty Then
                        .AddWithValue("@ProductModelNumber", System.DBNull.Value)
                    Else
                        .AddWithValue("@ProductModelNumber", Me.iProductModelNumber)
                    End If
                    If Me.iName = String.Empty Then
                        .AddWithValue("@Name", System.DBNull.Value)
                    Else
                        .AddWithValue("@Name", Me.iName)
                    End If
                    If Me.iDescription = String.Empty Then
                        .AddWithValue("@Description", System.DBNull.Value)
                    Else
                        .AddWithValue("@Description", Me.iDescription)
                    End If
                    If Me.iPicture Is Nothing Then
                        'need this code to prevent error
                        '(explicit type definition is required when parsing null to image columns)
                        Dim param As New SqlParameter("@Picture", SqlDbType.Image)
                        param.Value = System.DBNull.Value
                        .Add(param)
                    Else
                        Using ms As New IO.MemoryStream
                            Me.iPicture.Save(ms, Drawing.Imaging.ImageFormat.Png)
                            .Add(New SqlParameter("@Picture", SqlDbType.Image))
                            .Item("@Picture").Value = ms.ToArray
                        End Using
                    End If
                    If Me.iLMUserId = Guid.Empty Then
                        .AddWithValue("@LMUserId", System.DBNull.Value)
                    Else
                        .AddWithValue("@LMUserId", Me.iLMUserId)
                    End If
                    If Me.iLMDate = DateTime.MinValue Then
                        .AddWithValue("@LMDate", System.DBNull.Value)
                    Else
                        .AddWithValue("@LMDate", Me.iLMDate)
                    End If
                End With
                'Execute Stored Procedure
                resInt = cmd.ExecuteNonQuery()
            Catch ex As Exception
                resBool = False
                Throw ex
            End Try
            If resInt = 0 Then
                Throw New RequestedRecordDoesNotExistException("Requested changes failed to insert, no records were modified")
            End If
            Return resBool
        End Function
        Protected Friend Overrides Function Update() As Boolean
            'sp_[TableName]Update
            Dim resBool As Boolean = True
            Dim resInt As Integer
            Dim da As New SqlDataAdapter
            Dim cmd As New SqlCommand("sp_" & Me.iTableName.ToString.Replace("COMP_", "") & "Update")
            cmd.Connection = Me.iConnection
            If IsNothing(Me.iTransaction) = False Then cmd.Transaction = Me.iTransaction
            'Set command type to Stoed Procedure
            cmd.CommandType = CommandType.StoredProcedure
            'Validation or required parameters
            Try
                'Populate Stored Procedure parameters
                With cmd.Parameters
                    If Me.iId = Guid.Empty Then
                        .AddWithValue("@EquipmentId", System.DBNull.Value)
                    Else
                        .AddWithValue("@EquipmentId", Me.iId)
                    End If
                    If Me.iEquipmentType = "" Then
                        .AddWithValue("@EquipmentType", System.DBNull.Value)
                    Else
                        .AddWithValue("@EquipmentType", Me.iEquipmentType)
                    End If
                    If Me.iManufacturerId = Guid.Empty Then
                        .AddWithValue("@ManufacturerId", System.DBNull.Value)
                    Else
                        .AddWithValue("@ManufacturerId", Me.iManufacturerId)
                    End If
                    If Me.iManufacturerName = String.Empty Then
                        .AddWithValue("@ManufacturerName", System.DBNull.Value)
                    Else
                        .AddWithValue("@ManufacturerName", Me.iManufacturerName)
                    End If
                    If Me.iProductModelNumber = String.Empty Then
                        .AddWithValue("@ProductModelNumber", System.DBNull.Value)
                    Else
                        .AddWithValue("@ProductModelNumber", Me.iProductModelNumber)
                    End If
                    If Me.iName = String.Empty Then
                        .AddWithValue("@Name", System.DBNull.Value)
                    Else
                        .AddWithValue("@Name", Me.iName)
                    End If
                    If Me.iDescription = String.Empty Then
                        .AddWithValue("@Description", System.DBNull.Value)
                    Else
                        .AddWithValue("@Description", Me.iDescription)
                    End If
                    If Me.iPicture Is Nothing Then
                        'need this code to prevent error
                        '(explicit type definition is required when parsing null to image columns)
                        Dim param As New SqlParameter("@Picture", SqlDbType.Image)
                        param.Value = System.DBNull.Value
                        .Add(param)
                    Else
                        Using ms As New IO.MemoryStream
                            Me.iPicture.Save(ms, Drawing.Imaging.ImageFormat.Png)
                            .Add(New SqlParameter("@Picture", SqlDbType.Image))
                            .Item("@Picture").Value = ms.ToArray
                        End Using
                    End If
                    If Me.iLMUserId = Guid.Empty Then
                        .AddWithValue("@LMUserId", System.DBNull.Value)
                    Else
                        .AddWithValue("@LMUserId", Me.iLMUserId)
                    End If
                    If Me.iLMDate = DateTime.MinValue Then
                        .AddWithValue("@LMDate", System.DBNull.Value)
                    Else
                        .AddWithValue("@LMDate", Me.iLMDate)
                    End If
                End With
                'Execute Stored Procedure
                resInt = cmd.ExecuteNonQuery()
            Catch ex As Exception
                resBool = False
                Throw ex
            End Try
            If resInt = 0 Then
                Throw New RequestedRecordDoesNotExistException("Requested changes failed to update, no records were modified")
            End If
            Return resBool
        End Function
        Protected Friend Overrides Function ValidateOnDelete() As Boolean
            Return True
        End Function
        Protected Friend Overrides Function ValidateOnInsert() As Boolean
            Return True
        End Function
        Protected Friend Overrides Function ValidateOnUpdate() As Boolean
            Return True
        End Function
#End Region

#Region "Overrides"
        Public Overrides Function ToString() As String
            If Me.iName <> "" Then
                Return Me.iName
            Else
                Return Me.iId.ToString
            End If
        End Function
        Public Overrides Function ToDetailsListBoxItem() As WindowsExtendedControls.DetailListBoxItem

            Dim header As String = ""
            Dim details As String = ""
            Dim footer As String = ""
            If Me.iProductModelNumber <> "" Then
                header = Me.iProductModelNumber & " - " & Me.iName
            Else
                header = Me.iName
            End If
            If Me.iProductModelNumber <> "" Then
                details = Me.iProductModelNumber & vbNewLine & Me.iName & vbNewLine & Me.iDescription
            End If
            If Me.iManufacturerName <> "" Then
                footer = Me.iManufacturerName
            End If
            Return New WindowsExtendedControls.DetailListBoxItem(header, details, footer, Nothing, Me)

        End Function
#End Region

    End Class

    Public Class CatalogItemTypeCollection
        Inherits DBTableObjectCollectionBase

        Public DataFilters As New DataFilterTypeCollection(Of CatalogItemTypeFields)(Me.iTablename.ToString)
        Public DataSorters As New DataSorterTypeCollection(Of CatalogItemTypeFields)(Me.iTableName.ToString)

        Public Sub New()
            MyBase.New(TableNamesEnum.SQC_Catalog)
        End Sub

        Default Public Property Item(ByVal Index As Integer) As CatalogItemType
            Get
                Return CType(Me.List.Item(Index), CatalogItemType)
            End Get
            Set(ByVal value As CatalogItemType)
                Me.List.Item(Index) = value
            End Set
        End Property

        Protected Friend Overrides Function Load() As Boolean
            Return Me.Load("SELECT * FROM " & Me.iTableName.ToString & Me.DataFilters.GetSQL & Me.DataSorters.GetSQL)
        End Function

        Protected Friend Overloads Function Load(ByVal CustomFilter As String) As Boolean
            'Get data from DB
            Dim cmd As New SqlCommand
            cmd.Connection = Me.iConnection
            If IsNothing(Me.iTransaction) = False Then cmd.Transaction = Me.iTransaction
            Dim dt As DataTable
            Try
                cmd.CommandText = CustomFilter
                dt = Utility.GetList(cmd)
            Catch ex As Exception
                Throw ex
            End Try
            'Clear exising list
            Dim row As DataRow
            List.Clear()

            'Build new list
            Dim typeObject As CatalogItemType

            For Each row In dt.Rows
                typeObject = New CatalogItemType()
                typeObject.iConnection = Me.iConnection
                typeObject.iTransaction = Me.iTransaction
                If typeObject.Load(row) = True Then Me.List.Add(typeObject)
            Next
            Return True
        End Function

        Public Overloads Sub Sort(ByVal propertyToSort As CatalogItemTypeFields, ByVal sortOrder As SortOrder)
            ipropertyToSort = propertyToSort.ToString
            isortOrder = sortOrder
            Me.InnerList.Sort(Me)
        End Sub

    End Class

#Region "Enumerations"       'Enumerations
    Public Enum CatalogItemTypeFields
        EquipmentId
        EquipmentType
        ManufacturerId
        ProductModelNumber
        Name
        Description
        Picture
        LMUserId
        LMDate

    End Enum
#End Region

End Namespace